Assessment

How did antibiotic prescription trends vary pre-, mid-, and post-lockdown in different regions of Scotland?

#How did the prescription of the top 10 most prescribed antibiotics in 2019 change due to lockdown

#How did covid-19 pandemic and lockdown affect the prescription trends of the most common antibiotics in Scotland. (Would it be better to focus on the main lockdown (May 2020) or also compare between lockdowns?)

First I will load the appropriate libraries. Then, load, clean and filter the data.

First, I loaded the datasets and created a function to concisely use the same functions on different datasets. I learned how to do this from the website https://www.dataquest.io/blog/write-functions-in-r/. In this function I filtered for antibiotics by only looking at prescriptions with the BNFItemCode starting with 0501. Then I filtered out NAs, cleaned up the names and calculated the number of prescriptions. I then merged the datasets

may2020 <- read_csv(here("data","pitc202005.csv"))
may2024 <- read_csv(here("data","pitc202405.csv"))
may2019 <- read_csv(here("data","pitc201905.csv"))

may2019 <- may2019 %>% 
  rename("HBT" = HBT2014)

process_antibiotics <- function(data) {
  data %>%
    filter(str_detect(BNFItemCode, "^0501"), !is.na(BNFItemDescription)) %>% 
    mutate(BNFItemDescription = str_replace(BNFItemDescription, "_", " "),
           Antibiotic = str_extract(BNFItemDescription, "^[^ ]+")) #Extracts the first word, which is the antibiotic name
}
#I filtered for prescriptions with an item code beginning with 0501 because prescriptions with that item code are antibiotics (Open Prescribing, 2019).

may2019_processed <- process_antibiotics(may2019)
may2020_processed <- process_antibiotics(may2020)
may2024_processed <- process_antibiotics(may2024)

#I split this into 2 functions as later I want to group by HBT
antibiotics_by_name <- function(data2)  {
  data2 %>% 
    group_by(Antibiotic) %>%
    summarise(quantity_sum = sum(PaidQuantity), .groups = "drop") %>%
    arrange(desc(quantity_sum))
}

#I filtered for prescriptions with an item code beginning with 0501 because prescriptions with that item code are antibiotics (Open Prescribing, 2019).

may2019_processed <- antibiotics_by_name(may2019_processed)
may2020_processed <- antibiotics_by_name(may2020_processed)
may2024_processed <- antibiotics_by_name(may2024_processed)

I then prepared the data for joining by renaming Fluclox to Flucloxacillin. I then joined the data, and tidied it

may2020_processed <- may2020_processed %>% 
  mutate(Antibiotic = ifelse(Antibiotic == "FLUCLOX", "FLUCLOXACILLIN", Antibiotic))

may2019_processed <- may2019_processed %>% 
  mutate(Antibiotic = ifelse(Antibiotic == "FLUCLOX", "FLUCLOXACILLIN", Antibiotic))

antibiotics1920 <- full_join(may2019_processed, may2020_processed, by = "Antibiotic")
antibiotics192024 <- full_join(antibiotics1920, may2024_processed, by = "Antibiotic")

antibiotics192024 <- antibiotics192024 %>% 
  rename(
   "May 2019" = quantity_sum.x,
   "May 2020" = quantity_sum.y,
   "May 2024" = quantity_sum) %>% 
  slice(1:10)

antibiotics192024 <- antibiotics192024 %>% 
  mutate(Antibiotic = str_to_sentence(Antibiotic)) #I changed all the antibiotic names to only have the first letter capitalised. I learnt this from the R help page

I created a stacked bar graph to visually compare prescriptions

pivoted_antibiotics <- antibiotics192024 %>% 
  pivot_longer(
    cols = c("May 2019", "May 2020", "May 2024"), 
    names_to = "Year", 
    values_to = "Prescription_Number")

options(scipen=999)

antibiotics_graph <- pivoted_antibiotics %>% 
  ggplot(aes(x = Prescription_Number, y = Year, fill = Antibiotic)) +
  geom_bar(stat = "identity") +
  labs(title = "Prescriptions Sold for Each Antibiotic by Year") +
  scale_fill_brewer(palette = "Paired")
  theme_minimal()

## List of 136
##  $ line                            :List of 6
##   ..$ colour       : chr "black"
##   ..$ linewidth    : num 0.5
##   ..$ linetype     : num 1
##   ..$ lineend      : chr "butt"
##   ..$ arrow        : logi FALSE
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_line" "element"
##  $ rect                            :List of 5
##   ..$ fill         : chr "white"
##   ..$ colour       : chr "black"
##   ..$ linewidth    : num 0.5
##   ..$ linetype     : num 1
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_rect" "element"
##  $ text                            :List of 11
##   ..$ family       : chr ""
##   ..$ face         : chr "plain"
##   ..$ colour       : chr "black"
##   ..$ size         : num 11
##   ..$ hjust        : num 0.5
##   ..$ vjust        : num 0.5
##   ..$ angle        : num 0
##   ..$ lineheight   : num 0.9
##   ..$ margin       : 'margin' num [1:4] 0points 0points 0points 0points
##   .. ..- attr(*, "unit")= int 8
##   ..$ debug        : logi FALSE
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ title                           : NULL
##  $ aspect.ratio                    : NULL
##  $ axis.title                      : NULL
##  $ axis.title.x                    :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : NULL
##   ..$ size         : NULL
##   ..$ hjust        : NULL
##   ..$ vjust        : num 1
##   ..$ angle        : NULL
##   ..$ lineheight   : NULL
##   ..$ margin       : 'margin' num [1:4] 2.75points 0points 0points 0points
##   .. ..- attr(*, "unit")= int 8
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ axis.title.x.top                :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : NULL
##   ..$ size         : NULL
##   ..$ hjust        : NULL
##   ..$ vjust        : num 0
##   ..$ angle        : NULL
##   ..$ lineheight   : NULL
##   ..$ margin       : 'margin' num [1:4] 0points 0points 2.75points 0points
##   .. ..- attr(*, "unit")= int 8
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ axis.title.x.bottom             : NULL
##  $ axis.title.y                    :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : NULL
##   ..$ size         : NULL
##   ..$ hjust        : NULL
##   ..$ vjust        : num 1
##   ..$ angle        : num 90
##   ..$ lineheight   : NULL
##   ..$ margin       : 'margin' num [1:4] 0points 2.75points 0points 0points
##   .. ..- attr(*, "unit")= int 8
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ axis.title.y.left               : NULL
##  $ axis.title.y.right              :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : NULL
##   ..$ size         : NULL
##   ..$ hjust        : NULL
##   ..$ vjust        : num 1
##   ..$ angle        : num -90
##   ..$ lineheight   : NULL
##   ..$ margin       : 'margin' num [1:4] 0points 0points 0points 2.75points
##   .. ..- attr(*, "unit")= int 8
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ axis.text                       :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : chr "grey30"
##   ..$ size         : 'rel' num 0.8
##   ..$ hjust        : NULL
##   ..$ vjust        : NULL
##   ..$ angle        : NULL
##   ..$ lineheight   : NULL
##   ..$ margin       : NULL
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ axis.text.x                     :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : NULL
##   ..$ size         : NULL
##   ..$ hjust        : NULL
##   ..$ vjust        : num 1
##   ..$ angle        : NULL
##   ..$ lineheight   : NULL
##   ..$ margin       : 'margin' num [1:4] 2.2points 0points 0points 0points
##   .. ..- attr(*, "unit")= int 8
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ axis.text.x.top                 :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : NULL
##   ..$ size         : NULL
##   ..$ hjust        : NULL
##   ..$ vjust        : num 0
##   ..$ angle        : NULL
##   ..$ lineheight   : NULL
##   ..$ margin       : 'margin' num [1:4] 0points 0points 2.2points 0points
##   .. ..- attr(*, "unit")= int 8
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ axis.text.x.bottom              : NULL
##  $ axis.text.y                     :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : NULL
##   ..$ size         : NULL
##   ..$ hjust        : num 1
##   ..$ vjust        : NULL
##   ..$ angle        : NULL
##   ..$ lineheight   : NULL
##   ..$ margin       : 'margin' num [1:4] 0points 2.2points 0points 0points
##   .. ..- attr(*, "unit")= int 8
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ axis.text.y.left                : NULL
##  $ axis.text.y.right               :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : NULL
##   ..$ size         : NULL
##   ..$ hjust        : num 0
##   ..$ vjust        : NULL
##   ..$ angle        : NULL
##   ..$ lineheight   : NULL
##   ..$ margin       : 'margin' num [1:4] 0points 0points 0points 2.2points
##   .. ..- attr(*, "unit")= int 8
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ axis.text.theta                 : NULL
##  $ axis.text.r                     :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : NULL
##   ..$ size         : NULL
##   ..$ hjust        : num 0.5
##   ..$ vjust        : NULL
##   ..$ angle        : NULL
##   ..$ lineheight   : NULL
##   ..$ margin       : 'margin' num [1:4] 0points 2.2points 0points 2.2points
##   .. ..- attr(*, "unit")= int 8
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ axis.ticks                      : list()
##   ..- attr(*, "class")= chr [1:2] "element_blank" "element"
##  $ axis.ticks.x                    : NULL
##  $ axis.ticks.x.top                : NULL
##  $ axis.ticks.x.bottom             : NULL
##  $ axis.ticks.y                    : NULL
##  $ axis.ticks.y.left               : NULL
##  $ axis.ticks.y.right              : NULL
##  $ axis.ticks.theta                : NULL
##  $ axis.ticks.r                    : NULL
##  $ axis.minor.ticks.x.top          : NULL
##  $ axis.minor.ticks.x.bottom       : NULL
##  $ axis.minor.ticks.y.left         : NULL
##  $ axis.minor.ticks.y.right        : NULL
##  $ axis.minor.ticks.theta          : NULL
##  $ axis.minor.ticks.r              : NULL
##  $ axis.ticks.length               : 'simpleUnit' num 2.75points
##   ..- attr(*, "unit")= int 8
##  $ axis.ticks.length.x             : NULL
##  $ axis.ticks.length.x.top         : NULL
##  $ axis.ticks.length.x.bottom      : NULL
##  $ axis.ticks.length.y             : NULL
##  $ axis.ticks.length.y.left        : NULL
##  $ axis.ticks.length.y.right       : NULL
##  $ axis.ticks.length.theta         : NULL
##  $ axis.ticks.length.r             : NULL
##  $ axis.minor.ticks.length         : 'rel' num 0.75
##  $ axis.minor.ticks.length.x       : NULL
##  $ axis.minor.ticks.length.x.top   : NULL
##  $ axis.minor.ticks.length.x.bottom: NULL
##  $ axis.minor.ticks.length.y       : NULL
##  $ axis.minor.ticks.length.y.left  : NULL
##  $ axis.minor.ticks.length.y.right : NULL
##  $ axis.minor.ticks.length.theta   : NULL
##  $ axis.minor.ticks.length.r       : NULL
##  $ axis.line                       : list()
##   ..- attr(*, "class")= chr [1:2] "element_blank" "element"
##  $ axis.line.x                     : NULL
##  $ axis.line.x.top                 : NULL
##  $ axis.line.x.bottom              : NULL
##  $ axis.line.y                     : NULL
##  $ axis.line.y.left                : NULL
##  $ axis.line.y.right               : NULL
##  $ axis.line.theta                 : NULL
##  $ axis.line.r                     : NULL
##  $ legend.background               : list()
##   ..- attr(*, "class")= chr [1:2] "element_blank" "element"
##  $ legend.margin                   : 'margin' num [1:4] 5.5points 5.5points 5.5points 5.5points
##   ..- attr(*, "unit")= int 8
##  $ legend.spacing                  : 'simpleUnit' num 11points
##   ..- attr(*, "unit")= int 8
##  $ legend.spacing.x                : NULL
##  $ legend.spacing.y                : NULL
##  $ legend.key                      : list()
##   ..- attr(*, "class")= chr [1:2] "element_blank" "element"
##  $ legend.key.size                 : 'simpleUnit' num 1.2lines
##   ..- attr(*, "unit")= int 3
##  $ legend.key.height               : NULL
##  $ legend.key.width                : NULL
##  $ legend.key.spacing              : 'simpleUnit' num 5.5points
##   ..- attr(*, "unit")= int 8
##  $ legend.key.spacing.x            : NULL
##  $ legend.key.spacing.y            : NULL
##  $ legend.frame                    : NULL
##  $ legend.ticks                    : NULL
##  $ legend.ticks.length             : 'rel' num 0.2
##  $ legend.axis.line                : NULL
##  $ legend.text                     :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : NULL
##   ..$ size         : 'rel' num 0.8
##   ..$ hjust        : NULL
##   ..$ vjust        : NULL
##   ..$ angle        : NULL
##   ..$ lineheight   : NULL
##   ..$ margin       : NULL
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ legend.text.position            : NULL
##  $ legend.title                    :List of 11
##   ..$ family       : NULL
##   ..$ face         : NULL
##   ..$ colour       : NULL
##   ..$ size         : NULL
##   ..$ hjust        : num 0
##   ..$ vjust        : NULL
##   ..$ angle        : NULL
##   ..$ lineheight   : NULL
##   ..$ margin       : NULL
##   ..$ debug        : NULL
##   ..$ inherit.blank: logi TRUE
##   ..- attr(*, "class")= chr [1:2] "element_text" "element"
##  $ legend.title.position           : NULL
##  $ legend.position                 : chr "right"
##  $ legend.position.inside          : NULL
##  $ legend.direction                : NULL
##  $ legend.byrow                    : NULL
##  $ legend.justification            : chr "center"
##  $ legend.justification.top        : NULL
##  $ legend.justification.bottom     : NULL
##  $ legend.justification.left       : NULL
##  $ legend.justification.right      : NULL
##  $ legend.justification.inside     : NULL
##  $ legend.location                 : NULL
##  $ legend.box                      : NULL
##  $ legend.box.just                 : NULL
##  $ legend.box.margin               : 'margin' num [1:4] 0cm 0cm 0cm 0cm
##   ..- attr(*, "unit")= int 1
##  $ legend.box.background           : list()
##   ..- attr(*, "class")= chr [1:2] "element_blank" "element"
##  $ legend.box.spacing              : 'simpleUnit' num 11points
##   ..- attr(*, "unit")= int 8
##   [list output truncated]
##  - attr(*, "class")= chr [1:2] "theme" "gg"
##  - attr(*, "complete")= logi TRUE
##  - attr(*, "validate")= logi TRUE

antibiotics_graph <- antibiotics_graph %>% 
  ggplotly(tooltip = c("Prescription_Number", "Antibiotic"))
antibiotics_graph

plotly - interactive graphs in R

This data visulaisation shows that the overall number of prescirptions sold decreased during lockdown and was slightly lower before lockdown than after. It also shows that the top 10 most prescribed antibiotics were not the same in every year.

I then created a gt() table to show the number of prescriptions and calculate the total prescriptions per year

all_antibiotics_table <- antibiotics192024 %>%
  summarise(across(starts_with("May"), sum, na.rm = TRUE)) %>%  
  mutate(Antibiotic = "Total") %>%
  bind_rows(antibiotics192024, .)

all_antibiotics_table <- all_antibiotics_table %>%
  gt() %>% 
  cols_label(Antibiotic = "Antibiotic") %>% 
  cols_align(align = "center",
             columns = everything()) %>% 
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_column_labels(columns = everything())
  ) %>% 
  tab_header(
    title = "Affect of the Covid19 pandemic on the prescription of common antibiotics",
    subtitle = "Looking at the change of prescriptions for the top 10 most common antibiotics from before, during and after the longest Covid19 lockdown") %>% 
  tab_spanner(
    label = "Number of Prescriptions",
    columns = c("May 2019", "May 2020", "May 2024"))
all_antibiotics_table

Antibiotic	Number of Prescriptions
Affect of the Covid19 pandemic on the prescription of common antibiotics
Looking at the change of prescriptions for the top 10 most common antibiotics from before, during and after the longest Covid19 lockdown
Antibiotic	May 2019	May 2020	May 2024
Amoxicillin	2875868	1778491.0	3751932.0
Phenoxymethylpenicillin	1892108	968754.0	2616743.0
Flucloxacillin	1612959	1475567.0	1733244.0
Trimethoprim	782422	745904.0	696670.0
Erythromycin	661545	443204.0	582068.0
Lymecycline	623567	493844.0	538300.0
Oxytetracycline	572089	428371.0	277441.0
Doxycycline	447389	387163.0	616170.0
Co-amoxiclav	382770	349958.0	466694.0
Clarithromycin	377144	230282.1	493464.5
Total	10227861	7301538.1	11772726.5

I then created a faceted geospatial map to show which areas of Scotland were prescribed more antibiotics than other areas, and how this changed over covid

Assessment

Francesca

2024-11-01

How did antibiotic prescription trends vary pre-, mid-, and post-lockdown in different regions of Scotland?